package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.ArrayList;

import model.Reservation;

public class ReservationDAO extends ModelDAO {

	private Connection connection;
	
	public ReservationDAO (){
		super();
		connection = getConnection();
	}
	
	public void closeConnection(){
		super.closeConnection();
	}
	
	public Reservation select(int id) {
		Reservation r = null;
		try{
			String consulta = "SELECT * FROM RESERVATIONS WHERE ID=?";
			PreparedStatement statement = this.connection.prepareStatement(consulta);
			
			try{statement.setInt(1, id);}
			catch(Exception e){e.printStackTrace();}
			
			ResultSet result = statement.executeQuery();
			while(result.next()){
				r = new Reservation();
				r.setId(result.getInt(1));
				r.setCreated(result.getTimestamp(2));
				r.setModified(result.getTimestamp(3));
				r.setReservation_date(result.getTimestamp(4));
				r.setActive(result.getInt(5));
				r.setUser_name(result.getString(6));
			}
			result.close();
			statement.close();
		}
		catch(SQLException e){e.printStackTrace();}
		return r;
	}
	
	public void create(Reservation r){
		if(select(r.getId()) != null) return;
		
		try{
			String consulta = "INSERT INTO RESERVATIONS "
				+"(CREATED, ACTIVE, RESERVATION_DATE, USER_NAME, MODIFIED, ID)"
				+ "VALUES " + "(?,?,?,?,?,?)";
			PreparedStatement statement = this.connection.prepareStatement(consulta);
			
			if(r.getCreated()!=null)statement.setTimestamp(1,r.getCreated());
			else {statement.setNull(1, java.sql.Types.VARCHAR);}
			
			try{statement.setInt(2, r.getActive());}
			catch(Exception e){e.printStackTrace();}
			
			if(r.getReservation_date()!=null)statement.setTimestamp(3,r.getReservation_date());
			else {statement.setNull(3, java.sql.Types.VARCHAR);}
			
			if(r.getUser_name()!=null)statement.setString(4,r.getUser_name());
			else {statement.setNull(4, java.sql.Types.VARCHAR);}
			
			if(r.getModified()!=null)statement.setTimestamp(5,r.getModified());
			else {statement.setNull(5, java.sql.Types.VARCHAR);}
			
			if(maxId()==-1){
				try{statement.setInt(6, 0);}
				catch(Exception e){e.printStackTrace();}
			}
			else{
				try{statement.setInt(6, maxId()+1);}
				catch(Exception e){e.printStackTrace();}
			}
			
		    statement.executeUpdate();
		    statement.close();
		}
		catch(SQLException e){e.printStackTrace();}	
	}

	public void update(Reservation r){
		if(select(r.getId()) != null) return;
		
		try{
			String consulta = "UPDATE RESERVATIONS "+
				"SET CREATED = ?, ACTIVED = ?, RESERVATION_DATE = ?, USER_NAME = ?, MODIFIED = ?"+
				" WHERE ID = ?";
			PreparedStatement statement = this.connection.prepareStatement(consulta);

			if(r.getCreated()!=null)statement.setTimestamp(1,r.getCreated());
			else {statement.setNull(1, java.sql.Types.VARCHAR);}
			
			try{statement.setInt(2, r.getActive());}
			catch(Exception e){e.printStackTrace();}
			
			if(r.getReservation_date()!=null)statement.setTimestamp(3,r.getReservation_date());
			else {statement.setNull(3, java.sql.Types.VARCHAR);}
			
			if(r.getUser_name()!=null)statement.setString(4,r.getUser_name());
			else {statement.setNull(4, java.sql.Types.VARCHAR);}
			
			if(r.getModified()!=null)statement.setTimestamp(5,r.getModified());
			else {statement.setNull(5, java.sql.Types.VARCHAR);}
			
			try{statement.setInt(6, r.getId());}
			catch(Exception e){e.printStackTrace();}
			
			statement.executeUpdate();
		    statement.close();
		}
		catch(SQLException e){e.printStackTrace();}
	}
	
	public int maxId(){
		int maxId = -1;
		try{
			Statement statement = this.connection.createStatement();
			String consulta = "SELECT MAX(ID) FROM RESERVATIONS";
			ResultSet result = statement.executeQuery(consulta);
			while(result.next())
				maxId = result.getInt(1);
			result.close();
			statement.close();
		}
		catch(Exception e){e.printStackTrace();}
		return maxId;
	}
	
	public List<Reservation> getAll(){
		List<Reservation> reservaciones = new ArrayList<Reservation>();
		try{
			Statement statement = this.connection.createStatement();
			String consulta = "SELECT * FROM RESERVATIONS WHERE BUSY = 0 AND ACTIVE = 1";
			ResultSet results = statement.executeQuery(consulta);
			while(results.next()){
				Reservation r = new Reservation();
				r.setId(results.getInt(1));
				r.setCreated(results.getTimestamp(2));
				r.setModified(results.getTimestamp(3));
				r.setReservation_date(results.getTimestamp(4));
				r.setActive(results.getInt(5));
				r.setUser_name(results.getString(6));
				if(r.getActive()==1)
					reservaciones.add(r);
			}
			results.close();
			statement.close();
		}
		catch(SQLException e){e.printStackTrace();}
		return reservaciones;
	}
	
	public void delete(int id){
		Reservation r = select(id);
		r.setActive(0);
		update(r);
	}
}
